﻿Public Class clsDbIdoHyo

    Public Structure EInfo
        Dim szSysEquipID As String
        Dim szEquipID As String
        Dim szEquipNo As String
        Dim szKouteiCD As String
        Dim szEquipType As String
        Dim iTypeCode As Integer
        Dim iSocketFLAG As Integer
        Dim szKouteiCD1 As String
    End Structure

    Public Structure DB_Items
        Dim szKindName As String
        Dim szPbLayout As String
        Dim szDieSizeX As String
        Dim szDieSizeY As String
        Dim szChipArrange As String
        Dim szColet As String
        Dim szPocket As String
        Dim szNiddle As String
        Dim szPiece As String
        Dim szNozzle As String
        Dim szPaste As String
        Dim szCure As String
        Dim szFrameName As String
        Dim szFrameMark As String
    End Structure

    Private eqInfo As EInfo
    Private dbitem As DB_Items
    '蘇州対応　maruyama@tmc
    Private procCodeInfo As String()

    Public ReadOnly Property EquipInfo As EInfo
        Get
            EquipInfo = eqInfo
        End Get
    End Property

    Public ReadOnly Property DbItems As DB_Items
        Get
            DbItems = dbitem
        End Get
    End Property

    '蘇州対応　maruyama@tmc
    Public ReadOnly Property ProcCdInfo As String()
        Get
            ProcCdInfo = procCodeInfo
        End Get
    End Property

    ''' <summary>
    ''' 装置IDから装置情報を取得する
    ''' </summary>
    ''' <param name="szEquipID">装置ID</param>
    ''' <remarks></remarks>
    Public Sub GetEquipInfoByID(ByVal szEquipID As String)
        Dim szSql As String = ""
        szSql &= "SELECT * FROM ( "
        szSql &= "  SELECT "
        szSql &= "  EQP.SYS_EQUIP_ID as SYS_EQUIP_ID, "
        szSql &= "  EQP.EQUIP_ID as EQUIP_ID, "
        szSql &= "  EQP.EQUIP_NO as EQUIP_NO, "
        szSql &= "  EQP.SOCKET_FLAG as SOCKET_FLAG, "
        szSql &= "  CASE "
        szSql &= "  WHEN AB.LEVEL1 = 1 THEN EQP.KOUTEI_CODE " 'UPD MIAO 2018/10/16 
        szSql &= "  WHEN AB.LEVEL1  = 2 THEN EQP.KOUTEI_CODE1 " 'UPD MIAO 2018/10/16 
        szSql &= "  WHEN AB.LEVEL1  = 3 THEN EQP.KOUTEI_CODE2 " 'UPD MIAO 2018/10/16 
        szSql &= "  WHEN AB.LEVEL1  = 4 THEN EQP.KOUTEI_CODE3 " 'UPD MIAO 2018/10/16 
        szSql &= "  WHEN AB.LEVEL1  = 5 THEN EQP.KOUTEI_CODE4 " 'UPD MIAO 2018/10/16 
        szSql &= "  WHEN AB.LEVEL1  = 6 THEN EQP.KOUTEI_CODE5 " 'UPD MIAO 2018/10/16 
        szSql &= "  WHEN AB.LEVEL1  = 7 THEN EQP.KOUTEI_CODE6 " 'UPD MIAO 2018/10/16 
        szSql &= "  WHEN AB.LEVEL1  = 8 THEN EQP.KOUTEI_CODE7 " 'UPD MIAO 2018/10/16 
        szSql &= "  WHEN AB.LEVEL1  = 9 THEN EQP.KOUTEI_CODE8 " 'UPD MIAO 2018/10/16 
        szSql &= "  WHEN AB.LEVEL1  = 10 THEN EQP.KOUTEI_CODE9 " 'UPD MIAO 2018/10/16 
        szSql &= "  END AS KOUTEI_CD, "
        szSql &= "  EQP.KOUTEI_CODE1,"
        szSql &= "  FCT.STR_PARAM2 AS EQUIP_TYPE, "
        szSql &= "  EQP.EQUIP_TYPE AS TYPE_CODE "
        szSql &= "  FROM KAD.T_EQUIP_REG EQP "
        szSql &= "  LEFT OUTER JOIN "
        szSql &= "  KAD.T_FACTORY_INFO FCT "
        szSql &= "  ON FCT.PARAM_ID=EQP.EQUIP_TYPE+400, "
        'szSql &= "  GENERATE_SERIES(1,10) AS S(LEVEL)  "'DEL MIAO 2018/10/16 
        szSql &= "  (SELECT LEVEL as LEVEL1 FROM DUAL CONNECT BY LEVEL <= 10) AB  " 'ADD MIAO 2018/10/16 
        szSql &= "  WHERE EQP.EQUIP_ID = ? "
        szSql &= ") T1 " 'UPD MIAO 2018/10/16 
        szSql &= "WHERE T1.KOUTEI_CD IS NOT NULL"

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szEquipID)
        If Not IsNothing(dt) AndAlso dt.Rows.Count > 0 Then
            eqInfo.szSysEquipID = IIf(IsDBNull(dt.Rows(0)("SYS_EQUIP_ID")), String.Empty, dt.Rows(0)("SYS_EQUIP_ID"))
            eqInfo.szEquipID = IIf(IsDBNull(dt.Rows(0)("SYS_EQUIP_ID")), String.Empty, dt.Rows(0)("EQUIP_ID"))
            eqInfo.szEquipNo = IIf(IsDBNull(dt.Rows(0)("EQUIP_NO")), String.Empty, dt.Rows(0)("EQUIP_NO"))
            eqInfo.szKouteiCD = IIf(IsDBNull(dt.Rows(0)("KOUTEI_CD")), String.Empty, dt.Rows(0)("KOUTEI_CD"))
            eqInfo.szEquipType = IIf(IsDBNull(dt.Rows(0)("EQUIP_TYPE")), String.Empty, dt.Rows(0)("EQUIP_TYPE"))
            eqInfo.iTypeCode = IIf(IsDBNull(dt.Rows(0)("TYPE_CODE")), 0, dt.Rows(0)("TYPE_CODE"))
            eqInfo.iSocketFLAG = IIf(IsDBNull(dt.Rows(0)("SOCKET_FLAG")), 0, dt.Rows(0)("SOCKET_FLAG"))
            eqInfo.szKouteiCD1 = IIf(IsDBNull(dt.Rows(0)("KOUTEI_CODE1")), String.Empty, dt.Rows(0)("KOUTEI_CODE1"))

            ReDim procCodeInfo(dt.Rows.Count - 1)
            Dim i As Integer = 0
            For Each dr In dt.Rows
                procCodeInfo(i) = dr("KOUTEI_CD")
                i = i + 1
            Next

        Else
            eqInfo.szSysEquipID = 0
            eqInfo.szEquipID = ""
            eqInfo.szEquipNo = ""
            eqInfo.szKouteiCD = ""
            eqInfo.szEquipType = ""
            eqInfo.iTypeCode = 0
        End If
    End Sub

    ''' <summary>
    ''' 装置Noから装置情報を取得する
    ''' </summary>
    ''' <param name="szEquipNo">装置No</param>
    ''' <remarks></remarks>
    Public Sub GetEquipInfoByNo(ByVal szEquipNo As String)
        Dim szSql As String = ""
        szSql &= "SELECT "
        szSql &= "  EQP.SYS_EQUIP_ID as SYS_EQUIP_ID, "
        szSql &= "  EQP.EQUIP_ID as EQUIP_ID, "
        szSql &= "  EQP.EQUIP_NO as EQUIP_NO, "
        szSql &= "  EQP.KOUTEI_CODE AS KOUTEI_CD, "
        szSql &= "  FCT.STR_PARAM2 AS EQUIP_TYPE, "
        szSql &= "  EQP.EQUIP_TYPE AS TYPE_CODE "
        szSql &= "FROM KAD.T_EQUIP_REG EQP "
        szSql &= "LEFT OUTER JOIN "
        szSql &= "  KAD.T_FACTORY_INFO FCT "
        szSql &= "ON FCT.PARAM_ID=EQP.EQUIP_TYPE+400 "
        szSql &= "WHERE EQP.EQUIP_NO = ?"

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szEquipNo)
        If Not IsNothing(dt) AndAlso dt.Rows.Count > 0 Then
            eqInfo.szSysEquipID = IIf(IsDBNull(dt.Rows(0)("SYS_EQUIP_ID")), String.Empty, dt.Rows(0)("SYS_EQUIP_ID"))
            eqInfo.szEquipID = IIf(IsDBNull(dt.Rows(0)("SYS_EQUIP_ID")), String.Empty, dt.Rows(0)("EQUIP_ID"))
            eqInfo.szEquipNo = IIf(IsDBNull(dt.Rows(0)("EQUIP_NO")), String.Empty, dt.Rows(0)("EQUIP_NO"))
            eqInfo.szKouteiCD = IIf(IsDBNull(dt.Rows(0)("KOUTEI_CD")), String.Empty, dt.Rows(0)("KOUTEI_CD"))
            eqInfo.szEquipType = IIf(IsDBNull(dt.Rows(0)("EQUIP_TYPE")), String.Empty, dt.Rows(0)("EQUIP_TYPE"))
            eqInfo.iTypeCode = IIf(IsDBNull(dt.Rows(0)("TYPE_CODE")), 0, dt.Rows(0)("TYPE_CODE"))
        Else
            eqInfo.szSysEquipID = 0
            eqInfo.szEquipID = ""
            eqInfo.szEquipNo = ""
            eqInfo.szKouteiCD = ""
            eqInfo.szEquipType = ""
            eqInfo.iTypeCode = 0
        End If
    End Sub

    Public Sub GetDbTable(ByVal szLotNo As String) ', ByVal szWLotNo As String)
        Dim szSql As String = ""
        szSql &= "SELECT "
        szSql &= "  TA.HINMEI AS KIND_NM, "       '品名
        szSql &= "  TB.PBLAYOUT AS PB_LAYOUT, "   'PB LAYOUT
        szSql &= "  TB.JYOKEN02_X AS SIZE_X, "    'ダイサイズX
        szSql &= "  TB.JYOKEN02_Y AS SIZE_Y, "    'ダイサイズY
        szSql &= "  TB.JYOKEN19 AS CHIP_ARR, "    'チップアレンジ
        szSql &= "  TB.JYOKEN22 AS COLET, "       'コレット
        szSql &= "  TB.JYOKEN23 AS POCKET, "      'ポケット
        szSql &= "  TB.JYOKEN29 AS NIDDLE, "      '針
        szSql &= "  TB.JYOKEN30 AS PIECE, "       '駒
        szSql &= "  TB.JYOKEN24 AS NOZZLE, "      'ノズル
        szSql &= "  TB.JYOKEN25 AS PASTE, "       'ペースト
        szSql &= "  TB.JYOKEN27 AS CURE, "        'キュア
        szSql &= "  TB.JYOKEN17_1 AS FRRAME_NM, " 'フレーム名
        szSql &= "  TB.JYOKEN18 AS FRAME_MK "     'フレーム刻印
        szSql &= "FROM TRA.L_IDOHYO_A TA "
        szSql &= "LEFT OUTER JOIN  "
        szSql &= "  TRA.L_IDOHYO_B TB "
        szSql &= "ON TA.LOTNO=TB.LOTNO "
        szSql &= "WHERE TA.LOTNO=? "
        'szSql &= "AND TA.W_LOTNO=? "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, szLotNo) ', szWLotNo)
        If Not IsNothing(dt) AndAlso dt.Rows.Count > 0 Then
            dbitem.szKindName = IIf(IsDBNull(dt.Rows(0)("KIND_NM")), String.Empty, dt.Rows(0)("KIND_NM"))
            dbitem.szPbLayout = IIf(IsDBNull(dt.Rows(0)("PB_LAYOUT")), String.Empty, dt.Rows(0)("PB_LAYOUT"))
            dbitem.szDieSizeX = IIf(IsDBNull(dt.Rows(0)("SIZE_X")), String.Empty, dt.Rows(0)("SIZE_X"))
            dbitem.szDieSizeY = IIf(IsDBNull(dt.Rows(0)("SIZE_Y")), String.Empty, dt.Rows(0)("SIZE_Y"))
            dbitem.szChipArrange = IIf(IsDBNull(dt.Rows(0)("CHIP_ARR")), String.Empty, dt.Rows(0)("CHIP_ARR"))
            dbitem.szColet = IIf(IsDBNull(dt.Rows(0)("COLET")), String.Empty, dt.Rows(0)("COLET"))
            dbitem.szPocket = IIf(IsDBNull(dt.Rows(0)("POCKET")), String.Empty, dt.Rows(0)("POCKET"))
            dbitem.szNiddle = IIf(IsDBNull(dt.Rows(0)("NIDDLE")), String.Empty, dt.Rows(0)("NIDDLE"))
            dbitem.szPiece = IIf(IsDBNull(dt.Rows(0)("PIECE")), String.Empty, dt.Rows(0)("PIECE"))
            dbitem.szNozzle = IIf(IsDBNull(dt.Rows(0)("NOZZLE")), String.Empty, dt.Rows(0)("NOZZLE"))
            dbitem.szPaste = IIf(IsDBNull(dt.Rows(0)("PASTE")), String.Empty, dt.Rows(0)("PASTE"))
            dbitem.szCure = IIf(IsDBNull(dt.Rows(0)("CURE")), String.Empty, dt.Rows(0)("CURE"))
            dbitem.szFrameName = IIf(IsDBNull(dt.Rows(0)("FRRAME_NM")), String.Empty, dt.Rows(0)("FRRAME_NM"))
            dbitem.szFrameMark = IIf(IsDBNull(dt.Rows(0)("FRAME_MK")), String.Empty, dt.Rows(0)("FRAME_MK"))
        Else
            dbitem.szKindName = ""
            dbitem.szPbLayout = ""
            dbitem.szDieSizeX = ""
            dbitem.szDieSizeY = ""
            dbitem.szChipArrange = ""
            dbitem.szColet = ""
            dbitem.szPocket = ""
            dbitem.szNiddle = ""
            dbitem.szPiece = ""
            dbitem.szNozzle = ""
            dbitem.szPaste = ""
            dbitem.szCure = ""
            dbitem.szFrameName = ""
            dbitem.szFrameMark = ""
        End If
    End Sub

    'ADD 苗 2018/3/23 begin
    Public Function getPassword() As String
        Dim szSql As String = ""
        szSql &= "SELECT str_param "
        szSql &= "FROM kad.t_factory_info  "
        szSql &= "WHERE param_id = ? "

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, PARAM_PASSWORD)
        If Not IsNothing(dt) AndAlso dt.Rows.Count > 0 Then
            If Not IsDBNull(dt.Rows(0)(0)) Then
                Return dt.Rows(0)(0)
            End If
        End If
        Return PARAM_PASSWORD_DEFAULT
    End Function
    'ADD 苗 2018/3/23 end
    Public Sub updateSocketFLAG(ByVal iSocketFlag As String, ByVal szEquipID As String)

        Dim szSql As String = ""

        szSql &= "UPDATE KAD.T_EQUIP_REG "
        szSql &= "SET "
        szSql &= "  SOCKET_FLAG      ='" & iSocketFlag & "' "
        szSql &= " WHERE EQUIP_ID ='" & szEquipID & "' "

        Dim cnt As Integer
        cnt = DBacs.ExecuteUpdateSql(szSql, -1)

    End Sub
    Public Sub updateKouteiCD1(KTC1 As String, szEquipID As String)
        Dim szSql As String = ""

        szSql &= "UPDATE KAD.T_EQUIP_REG "
        szSql &= "SET "
        szSql &= "  KOUTEI_CODE1      ='" & KTC1 & "' "
        szSql &= " WHERE EQUIP_ID ='" & szEquipID & "' "

        Dim cnt As Integer
        cnt = DBacs.ExecuteUpdateSql(szSql, -1)
    End Sub
End Class
